﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Data;
namespace HCDV.DB
{
    public class DbMySql : Idb
    {
        private static string connectionString = BaseConfig.ConnectionString;
        /// <summary>
        /// 获取主键
        /// </summary>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public long GetSequence(string fieldName)
        {
            MySqlParameter parmOut = new MySqlParameter();
            parmOut.Direction = System.Data.ParameterDirection.Output;
            parmOut.DbType = System.Data.DbType.Int32;
            parmOut.ParameterName = "?v_seq_value";
            MySqlParameter parmIn = new MySqlParameter();
            parmIn.Direction = System.Data.ParameterDirection.Input;
            parmIn.DbType = System.Data.DbType.String;
            parmIn.ParameterName = "?v_seq_name";
            parmIn.Value = fieldName;
            try
            {
                MySqlCommand command = new MySqlCommand();
                command.Parameters.Add(parmIn);
                command.Parameters.Add(parmOut);
                command.CommandText = "get_sequence";
                command.Connection = new MySqlConnection(connectionString);
                command.CommandType = CommandType.StoredProcedure;
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                command.ExecuteNonQuery();
                return long.Parse(parmOut.Value.ToString());
            }
            catch (Exception ex)
            {
                throw ex;

            }

        }

        /// <summary>
        /// 调用proc
        /// </summary>
        /// <param name="procname"></param>
        /// <param name="allinfo"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool doProc(string procname, Hashtable allinfo, out string info)
        {
            info = "";
            try
            {
                MySqlCommand command = new MySqlCommand();
                command.CommandText = procname;
                command.Connection = new MySqlConnection(connectionString);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("outinfo", MySqlDbType.VarChar, 100);
                //设置参数
                IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
                string value = "";
                string fieldname = "";
                while (en.MoveNext())
                {
                    fieldname = en.Key.ToString();
                    value = en.Value.ToString() == "" ? null : en.Value.ToString();
                    command.Parameters.Add(fieldname, MySqlDbType.VarChar, 200);
                    command.Parameters[fieldname].Value = value;

                }
                command.Parameters["outinfo"].Direction = System.Data.ParameterDirection.Output;
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                command.ExecuteNonQuery();
                info = command.Parameters["outinfo"].Value.ToString();
                return true;

            }
            catch (Exception ex)
            {
                info = ex.Message;
                throw ex;

            }
        }
        public DataSet getProc(string procname, Hashtable allinfo)
        {

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlCommand cm = new MySqlCommand();
                    cm.Connection = connection;
                    cm.CommandText = procname;
                    cm.CommandType = CommandType.StoredProcedure;
                    IDataParameter[] parameteres = new MySqlParameter[allinfo.Count];
                    //设置参数
                    IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
                    int ip = 0;
                    while (en.MoveNext())
                    {
                        parameteres[ip] = new MySqlParameter("@" + en.Key.ToString(), (en.Value.ToString() == "" ? null : en.Value.ToString()));
                        ip++;
                    }
                    cm.Parameters.AddRange(parameteres);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cm);
                    adapter.Fill(ds);
                    cm.Dispose();
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {

                    connection.Close();
                }
            }


        }
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int spExe(string sql)
        {
           
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (Exception e)
                    {
                        LogHelper.LogInfo("执行sql spExe:" + sql+"  "+e.Message);
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public double spExeBackid(string sql)
        {

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, connection))
                {
                    try
                    {
                        DataSet ds = new DataSet();
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        if (rows > 0)
                        {
                            MySqlDataAdapter command = new MySqlDataAdapter("SELECT LAST_INSERT_ID();", connection);
                            command.Fill(ds, "ds");
                            DataTable dt = ds.Tables[0];
                            double re = double.Parse(dt.Rows[0][0].ToString());
                            return re;
                        }
                        else return -1;
                    }
                    catch (Exception e)
                    {
                        LogHelper.LogInfo("执行sql spExeBackid:" + sql + "  " + e.Message);
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 查询SQL
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataTable query(string SQLString)
        {
          
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (Exception ex)
                {
                    LogHelper.LogInfo("执行sql :" + SQLString + "  " + ex.Message);
                    throw new Exception(ex.Message);
                }
                return ds.Tables[0];
            }
        } /// <summary>
        /// 查询SQL
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataSet queryds(string SQLString)
        {
          
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (Exception ex)
                {
                    LogHelper.LogInfo("执行sql queryds:" + SQLString+" "+ex.Message);
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 通用查询-分页
        /// </summary>
        /// <param name="tableinfo"></param>
        /// <param name="returnfiles"></param>
        /// <param name="startp"></param>
        /// <param name="endp"></param>
        /// <param name="where"></param>
        /// <param name="pall"></param>
        /// <param name="orders"></param>
        /// <returns></returns>
        public DataTable getPage(string tableinfo, string returnfiles, string startp, string endp, string where, ref double pall, string orders = null)
        {
            if (endp == "all")
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT  ");
                sb.Append(returnfiles + ",0 as rowhcindex");
                sb.Append(" FROM ");
                sb.Append(tableinfo);
                if (!string.IsNullOrEmpty(where))
                {
                    sb.Append(" where ");
                    sb.Append(where);
                }
                if (!string.IsNullOrEmpty(orders))
                { sb.Append(" order by " + orders); }
                sb.Append(";");

                #region count(*)
                StringBuilder sb_count = new StringBuilder();
                sb_count.Append("SELECT Count(*) ");
                sb_count.Append(" FROM ");
                sb_count.Append(tableinfo);
                if (!string.IsNullOrEmpty(where))
                {
                    sb_count.Append(" where ");
                    sb_count.Append(where);
                }
                sb_count.Append(";");
                sb.Append(sb_count.ToString());
                #endregion


                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                      
                        connection.Open();
                        MySqlDataAdapter command = new MySqlDataAdapter(sb.ToString(), connection);
                        command.Fill(ds, "ds");
                        pall = double.Parse(ds.Tables[1].Rows[0][0].ToString());
                        DataTable dt = ds.Tables[0];
                        int i = 1;
                        foreach (DataRow dr in dt.Rows)
                        {
                            dr["rowhcindex"] = i++;
                        }
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        LogHelper.LogInfo("执行sql getPage:" + sb.ToString()+  " "+ex.Message);
                        throw new Exception(ex.Message);
                    }

                }
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT  ");
                sb.Append(returnfiles + ",0 as rowhcindex");
                sb.Append(" FROM ");
                sb.Append(tableinfo);
                if (!string.IsNullOrEmpty(where))
                {
                    sb.Append(" where ");
                    sb.Append(where);
                }
                if (!string.IsNullOrEmpty(orders))
                { sb.Append(" order by " + orders); }
                sb.Append(" LIMIT ");
                sb.Append(int.Parse(startp) - 1);
                sb.Append(" , ");
                sb.Append(int.Parse(endp) - int.Parse(startp) + 1);
                sb.Append(";");
                //sb.Append("SELECT FOUND_ROWS();");
                #region count(*)
                StringBuilder sb_count = new StringBuilder();
                sb_count.Append("SELECT Count(*) ");
                sb_count.Append(" FROM ");
                sb_count.Append(tableinfo);
                if (!string.IsNullOrEmpty(where))
                {
                    sb_count.Append(" where ");
                    sb_count.Append(where);
                }
                sb_count.Append(";");
                if (pall == 0 || startp == "1")//第一页 或者没有记录数
                {
                    sb.Append(sb_count.ToString());
                }
                #endregion

                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                     
                        connection.Open();
                        MySqlDataAdapter command = new MySqlDataAdapter(sb.ToString(), connection);
                        command.Fill(ds, "ds");
                        if ((pall == 0 || startp == "1") && ds.Tables[0].Rows.Count>0)//第一页 或者没有记录数
                        {
                            pall = double.Parse(ds.Tables[1].Rows[0][0].ToString());
                        }
                       
                        DataTable dt = ds.Tables[0];
                        int i = int.Parse(startp);
                        foreach (DataRow dr in dt.Rows)
                        {
                            dr["rowhcindex"] = i++;
                        }
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        LogHelper.LogInfo("执行sql getPage:" + sb.ToString() + " " + ex.Message);
                        throw new Exception(ex.Message);
                    }

                }
            }

        }

        public DataTable getPageInfo(string tableinfo, string returnfiles, string startp, string endp, string where, ref int pall, string orders = null)
        {
            double palld = pall;
            DataTable dt = getPage(tableinfo, returnfiles, startp, endp, where, ref  palld, orders);
            int.TryParse(palld.ToString(), out pall);
            return dt;

        }
        /// <summary>
        /// 通用查询-不分页
        /// </summary>
        /// <param name="tableinfo"></param>
        /// <param name="returnfiles"></param>
        /// <param name="orders"></param>
        /// <returns></returns>
        public DataTable getInfo(string tableinfo, string returnfiles, string where, string orders = null)
        {
            double pall = 0;
            return getPage(tableinfo, returnfiles, "0", "all", where, ref  pall, orders);
        }



        private string paramChg(string str)
        {
            str = "?" + str;
            return str;
        }





        /// <summary>
        /// 工作流查询
        /// </summary>
        public DataTable getPageInfohf(LoginUser l, string ywcode, string tableinfo, string returnfiles, string startp, string endp, string where, out int pall, string orders = null)
        {
            double palld;
            DataTable dt = getPagehf(l, ywcode, tableinfo, returnfiles, startp, endp, where, out  palld, orders);
            int.TryParse(palld.ToString(), out pall);
            return dt;
        }
        public DataTable getInfohf(LoginUser l, string ywcode, string tableinfo, string returnfiles, string where, string orders = null)
        {
            double pall;
            return getPagehf(l, ywcode, tableinfo, returnfiles, "0", "all", where, out  pall, orders);
        }
        /// <summary>
        /// 工作流查询
        /// </summary>
        public DataTable getPagehf(LoginUser l, string ywcode, string tableinfo, string returnfiles, string startp, string endp, string where, out double pall, string orders = null)
        {
            if (endp == "all")
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT SQL_CALC_FOUND_ROWS ");
                sb.Append(returnfiles);
                sb.Append(@",hcr_w_run.fdeid as hcrdeid,hcr_w_run.fnid as hcrnid,hcr_w_run.fnname as hcrnname,hcr_w_run.fntype as hcrntype,hcr_w_run.faddtime as hcraddtime,
IFNULL(hcr_w_run.fstates,'待发起')  as hcrstates,hcr_w_run.fywstate as hcrywstate,hcr_w_run.fsetuptime as hcrsetuptime, hcr_w_de.fdename  as hcrdename,hcr_w_next.fdourl ");
                sb.Append(" FROM ");
                sb.Append(tableinfo);
                sb.Append(" left join hcr_w_run on hcr_w_run.fruntype='" + ywcode + "'  AND  t.id= hcr_w_run.frunywid  ");
                sb.Append(" left join hcr_w_next on hcr_w_run.fnid=hcr_w_next.id   ");
                sb.Append(" left join  hcr_w_de on  hcr_w_de.id=hcr_w_run.fdeid  ");
                //sb.Append(" left join hcr_w_run on hcr_w_run.fruntype='" + ywcode + "'  AND  t.id= hcr_w_run.frunywid   left join  hcr_w_de on  hcr_w_de.id=hcr_w_run.fdeid  ");
                if (!string.IsNullOrEmpty(where))
                {
                    sb.Append(" where ");
                    sb.Append(where);
                }
                if (!string.IsNullOrEmpty(orders))
                { sb.Append(" order by " + orders); }
                sb.Append(";");
                sb.Append("SELECT FOUND_ROWS();");
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        MySqlDataAdapter command = new MySqlDataAdapter(sb.ToString(), connection);
                        command.Fill(ds, "ds");
                        pall = double.Parse(ds.Tables[1].Rows[0][0].ToString());
                        return ds.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }

                }
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT SQL_CALC_FOUND_ROWS ");
                sb.Append(returnfiles);
                sb.Append(@",hcr_w_run.fdeid as hcrdeid,hcr_w_run.fnid as hcrnid,hcr_w_run.fnname as hcrnname,hcr_w_run.fntype as hcrntype,hcr_w_run.faddtime as hcraddtime,
IFNULL(hcr_w_run.fstates,'待发起')  as hcrstates,hcr_w_run.fywstate as hcrywstate,hcr_w_run.fsetuptime as hcrsetuptime, hcr_w_de.fdename  as hcrdename,hcr_w_next.fdourl ");
                sb.Append(" FROM ");
                sb.Append(tableinfo);
                sb.Append(" left join hcr_w_run on hcr_w_run.fruntype='" + ywcode + "'  AND  t.id= hcr_w_run.frunywid  ");
                sb.Append(" left join hcr_w_next on hcr_w_run.fnid=hcr_w_next.id   ");
                sb.Append(" left join  hcr_w_de on  hcr_w_de.id=hcr_w_run.fdeid  ");
                if (!string.IsNullOrEmpty(where))
                {
                    sb.Append(" where ");
                    sb.Append(where);
                }
                if (!string.IsNullOrEmpty(orders))
                { sb.Append(" order by " + orders); }
                sb.Append(" LIMIT ");
                sb.Append(int.Parse(startp) - 1);
                sb.Append(" , ");
                sb.Append(int.Parse(endp) - int.Parse(startp) + 1);
                sb.Append(";");
                sb.Append("SELECT FOUND_ROWS();");
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        MySqlDataAdapter command = new MySqlDataAdapter(sb.ToString(), connection);
                        command.Fill(ds, "ds");
                        pall = double.Parse(ds.Tables[1].Rows[0][0].ToString());
                        return ds.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }

                }
            }
        }
    }
}
